package homework.jdbc.dao;

import jdbctest.ConnectionTest;
import jdbctest.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/9/13.
 */
public class UserDao {//////
    public List<User> findAll(){
        Connection conn=JDBCUtils.getConnection();
        List<User> list=new ArrayList<>();
        ResultSet result=null;
        Statement statement=null;
        try {
            statement=conn.createStatement();
            String sql="select * from tablename1";
            result=statement.executeQuery(sql);
            while (result.next()){
                User user=new User();
                user.setId(result.getInt(1));
                user.setName(result.getString(2));
                user.setSb(result.getString(3));
                list.add(user);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (result != null){
                try {
                    result.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
    public boolean insert(User user){
        Connection conn=JDBCUtils.getConnection();
        String sql="insert into tablename1(id,name,sb) values(?,?,?)";
        PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,user.getId());
            ps.setString(2,user.getName());
            ps.setString(3,user.getSb());

            int num=ps.executeUpdate();
            if(num == 1){
                return true;
            }
        }catch (SQLException e){e.printStackTrace();}
        return false;
    }
    public boolean update(User user){
        String sql="update user set name=?,sb=? where id=?";
        return false;
    }
    public boolean delete(int id){
        Connection conn=JDBCUtils.getConnection();
        String sql="delete from tablename1 where id=?";
        return false;
    }
    public User findById(int id){
        Connection conn=JDBCUtils.getConnection();
        User user=new User();
        ResultSet result=null;
        //Statement statement=null;
        PreparedStatement ps=null;
        try {
            //statement=conn.createStatement();
            //String sql="select * from tablename1 where id=2";
            String sql="select * from tablename1 where id=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            //result=statement.executeQuery(sql);
            result=ps.executeQuery();
            while (result.next()){
                user.setId(result.getInt(1));
                user.setName(result.getString(2));
                user.setSb(result.getString(3));
                return user;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            if (result != null){
                try {
                    result.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        if (ps != null){
            try {
                ps.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if (conn != null){
            JDBCUtils.close();
        }

        return null;
    }
}
